package com.mike.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Session;
import org.springframework.stereotype.Repository;

import com.mike.dao.BaseDao;
import com.mike.dao.introPeopleDao;
import com.mike.pojo.PeopleIntro;
import com.mike.util.JDBCTools;

@Repository
public class introPeopleDaoImpl extends BaseDao implements introPeopleDao {

	@Override
	public List<PeopleIntro> query() {
		Connection conn = JDBCTools.getConnection();
		// SELECT t1.*, t2.name FROM (SELECT intro, AVG(sal) sal, COUNT(*)
		// counts FROM t_emp GROUP BY intro ) t1 INNER JOIN (SELECT NAME,intro
		// FROM t_emp WHERE rank='经理' GROUP BY intro) t2 ON t1.intro = t2.intro
		String sql = "SELECT t1.*, t2.name FROM (SELECT intro,  AVG(sal) sal, COUNT(*) counts FROM t_emp GROUP BY intro ) t1 INNER JOIN (SELECT NAME,intro FROM t_emp WHERE rank='经理' GROUP BY intro) t2 ON t1.intro = t2.intro";
		List<PeopleIntro> list = new ArrayList<>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				PeopleIntro p = new PeopleIntro();
				/*
				 * private String intro; private int sal; private int counts;
				 * private String name;
				 */
				p.setIntro(rs.getString(1));
				p.setSal(rs.getInt(2));
				p.setCounts(rs.getInt(3));
				p.setName(rs.getString(4));
				list.add(p);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCTools.closeConnection(conn, ps, rs);
		return list;

	}

	@Override
	public List<PeopleIntro> query1() {//必须要创建与sql相对应的pojo，生成废表
		Session session = getSession();
		String sql = "SELECT intro,  AVG(sal) sal, COUNT(*) counts FROM t_emp GROUP BY intro ";
		List<PeopleIntro> a = session.createSQLQuery(sql).addEntity(PeopleIntro.class).list();

		String sql2 = "SELECT NAME,intro FROM t_emp WHERE rank='经理' GROUP BY intro";
		List<PeopleIntro> a2 = session.createSQLQuery(sql2).addEntity(PeopleIntro.class).list();
		session.close();
		a.addAll(a2);
		return a;

	}

}
